package com.es.business.citys;

import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.HashMap;

import com.es.business.area.AreaBean;
import com.es.business.area.AreaBiz;
import com.es.util.db.Dao;
import com.es.util.page.PageBean;
import com.es.util.page.PageNavigator;

public class CitysBiz {
	public boolean insertCitys(String code, String value) throws Exception{
		Dao dao = new Dao();
		boolean result = false;
		String sql = "insert into hrm_citys (citys_code, citys_name) values(?,?)";
		ArrayList<String> sqlParam = new ArrayList<String>();
		sqlParam.add(code);
		sqlParam.add(value);
		try{
			dao.update(sql, sqlParam);
			result = true;
		}catch(Exception ex){
			throw ex;
		}finally{
			dao.close();
		}
		return result;
	}
	
	public PageBean searchCitys(String code, String value, int curpage) throws Exception{
		Dao dao = new Dao();
		PageBean pb = null;
		String sql = "select citys_id, citys_code, citys_name from hrm_citys ";
		String[] sqlp = {" citys_code = ? ", " citys_name = ? "};
		String and = " and ";
		String where = " where ";
		ArrayList<String> sqlParam = new ArrayList<String>();
		ArrayList<String> sqlParam2 = new ArrayList<String>();
		sqlParam2.add(code);
		sqlParam2.add(value);
		boolean flag = false;
		for(int i = 0; i < sqlParam2.size(); ++i){
			String param = sqlParam2.get(i);
			if(param != null && param.length() > 0){
				if(flag){
					sql += and + sqlp[i];	
				}else{
					sql += where + sqlp[i];
					flag = true;
				}
				sqlParam.add(param);
			}
		}
		try{
			PageNavigator pageNav = new PageNavigator();
			int totalC = dao.getAllRow(sql, sqlParam);
			pageNav.setRowTotal(totalC);
			pageNav.setCurpage(curpage);
			if(totalC % pageNav.getPageRow() == 0){
				pageNav.setPageTotal(totalC / pageNav.getPageRow());
			}else{
				pageNav.setPageTotal(totalC / pageNav.getPageRow() + 1);
			}
			pb = dao.queryPageList(sql, pageNav, sqlParam);
			pb.setParam(sqlParam2);
		}catch(Exception ex){
			throw ex;
		}finally{
			dao.close();
		}
		return pb;
	}
	
	public CitysBean getCitysById(String id) throws Exception{
		Dao dao = new Dao();
		CitysBean result = null;
		String sql = "select citys_code, citys_name from hrm_citys where citys_id = ?";
		ArrayList<String> sqlParam = new ArrayList<String>();
		sqlParam.add(id);
		try{
			ResultSet rs = dao.Select(sql, sqlParam);
			while(rs.next()){
				result = new CitysBean();
				result.setCitysId(id);
				result.setCitysCode(rs.getString(1));
				result.setCitysValue(rs.getString(2));
			}
		}catch(Exception ex){
			throw ex;
		}finally{
			dao.close();
		}
		return result;
	}
	
	public CitysBean getCitysByCode(String code) throws Exception{
		Dao dao = new Dao();
		CitysBean result = new CitysBean();
		String sql = "select citys_id, citys_name from hrm_citys where citys_code = ?";
		ArrayList<String> sqlParam = new ArrayList<String>();
		sqlParam.add(code);
		try{
			ResultSet rs = dao.Select(sql, sqlParam);
			while(rs.next()){
				result.setCitysId(rs.getString(1));
				result.setCitysCode(code);
				result.setCitysValue(rs.getString(2));
			}
		}catch(Exception ex){
			throw ex;
		}finally{
			dao.close();
		}
		return result;
	}
	
	public boolean modifyCitysById(String code, String value, String id) throws Exception{
		Dao dao = new Dao();
		boolean result = false;
		String sql = "update hrm_citys set citys_code =?, citys_name =? where citys_id =?";
		ArrayList<String> sqlParam = new ArrayList<String>();
		sqlParam.add(code);
		sqlParam.add(value);
		sqlParam.add(id);
		try{
			dao.update(sql, sqlParam);
			result = true;
		}catch(Exception ex){
			throw ex;
		}finally{
			dao.close();
		}
		return result;
	}
	
	public boolean deleteCitysById(String id) throws Exception{
		Dao dao = new Dao();
		boolean result = false;
		String sql = "delete from hrm_citys where citys_id = ?";
		ArrayList<String> sqlParam = new ArrayList<String>();
		sqlParam.add(id);
		try{
			dao.update(sql, sqlParam);
			result = true;
		}catch(Exception ex){
			throw ex;
		}finally{
			dao.close();
		}
		return result;
	}
	
	public ArrayList<CitysBean> getCitysList() throws Exception{
		Dao dao = new Dao();
		ArrayList<CitysBean> result = new ArrayList<CitysBean>();
		String sql = "select citys_id, citys_code, citys_name from hrm_citys";
		ArrayList<String> sqlParam = new ArrayList<String>();
		try{
			ResultSet rs = dao.Select(sql, sqlParam);
			while(rs.next()){
				CitysBean citysBean = new CitysBean();
				citysBean.setCitysId(rs.getString(1));
				citysBean.setCitysCode(rs.getString(2));
				citysBean.setCitysValue((rs.getString(3)));
				result.add(citysBean);
			}
		}catch(Exception ex){
			throw ex;
		}finally{
			dao.close();
		}
		return result;
	}
}
